package com.web.surf.sample.eshop.database.dao.services;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import com.web.surf.sample.eshop.database.dao.interfaces.ISectionDao;
import com.web.surf.sample.eshop.faces.core.AbstractBean;
import com.web.surf.sample.eshop.faces.managed.beans.SectionBean;
import com.web.surf.sample.eshop.faces.managed.beans.SectionBean.SectionProperties;

public class SectionDaoImpl extends AbstractDao implements ISectionDao {

	private final String SQL_SECTIONS = "SELECT cat_name, cat_id, sub_name, sub_id " +
			"FROM categories AS c,sections AS s WHERE s.cat_id=c.cat_id";
	private static final String SQL_SUB_BY_CATID = "SELECT * " +
			"FROM categories AS c,sections AS s WHERE s.cat_id=? AND  s.cat_id=c.cat_id";
	private static final String SQL_SUB_BYID = "SELECT * " +
	"FROM categories AS c,sections AS s WHERE s.cat_id=c.cat_id AND s.sub_id=?";
	private static final String SQL_CATEGORIES = "SELECT * FROM categories";
	
	private String marker;
	private static final String CATEGORIES_MARKER = "categories";
	
	public List<AbstractBean> getSections() {
		try {
			marker="";
			PreparedStatement getCategories = getConnection()
						.prepareStatement(SQL_SECTIONS);
			return executeSelect(getCategories);
		} catch (SQLException e) {
			terminateConnection();
			return null;
		}	
	}
	
	public List<AbstractBean> getCategories() {
		try {
			marker=CATEGORIES_MARKER;
			PreparedStatement getCategories = getConnection()
						.prepareStatement(SQL_CATEGORIES);
			return executeSelect(getCategories);
		} catch (SQLException e) {
			terminateConnection();
			return null;
		}	
	}
	
	public List<AbstractBean> getSubcategoriesByCategoryID(String categoryID) {
		try {
			marker="";
	//		if (categoryID==null) {categoryID = "0";}
			PreparedStatement subcategoriesByCategorySQL = getConnection()
					.prepareStatement(SQL_SUB_BY_CATID);
			subcategoriesByCategorySQL.setString(1, categoryID);
			return executeSelect(subcategoriesByCategorySQL);
		} catch (SQLException e) {
			terminateConnection();
			return null;
		}
	}
	
	public AbstractBean getSubcategoryByID(String categoryID) {
		try {
			marker="";
			PreparedStatement subcategoriesByCategorySQL = getConnection()
					.prepareStatement(SQL_SUB_BYID);
			subcategoriesByCategorySQL.setString(1, categoryID);
			return executeSelect(subcategoriesByCategorySQL).get(0);
		} catch (SQLException e) {
			terminateConnection();
			return null;
		}
	}
	
	protected Map<?, ?> processMapSelect(ResultSet rs) throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}
	
	protected int processSelectCount(ResultSet rs) throws SQLException {
		// TODO Auto-generated method stub
		return 0;
	}
	
	protected List<AbstractBean> processSelectResult(ResultSet rs)
			throws SQLException {		
		if (!CATEGORIES_MARKER.equals(marker)) {
			List<AbstractBean> resultList = new ArrayList<AbstractBean>();
			while (rs.next()) {
				AbstractBean section = new SectionBean();

				String current = rs.getString(SectionProperties.CATEGORY_ID.getDatabaseName());
				section.addProperty(SectionProperties.CATEGORY_ID, current);
			
				current = rs.getString(SectionProperties.CATEGORY_NAME.getDatabaseName());
				section.addProperty(SectionProperties.CATEGORY_NAME, current);
				
				current = rs.getString(SectionProperties.SUBCATEGORY_ID.getDatabaseName());
				section.addProperty(SectionProperties.SUBCATEGORY_ID, current);
			
				current = rs.getString(SectionProperties.SUBCATEGORY_NAME.getDatabaseName());
				section.addProperty(SectionProperties.SUBCATEGORY_NAME, current);			
				
				resultList.add(section);
		}
		return resultList;
		}   else {
			List<AbstractBean> resultList = new ArrayList<AbstractBean>();
			while (rs.next()) {
				AbstractBean section = new SectionBean();

				String current = rs.getString(SectionProperties.CATEGORY_ID.getDatabaseName());
				section.addProperty(SectionProperties.CATEGORY_ID, current);
				
				current = rs.getString(SectionProperties.CATEGORY_NAME.getDatabaseName());
				section.addProperty(SectionProperties.CATEGORY_NAME, current);
				
				resultList.add(section);
		}	
			return resultList;
	}
	}
}